package com.supermap.application.utils;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.omg.CORBA.OBJ_ADAPTER;
import org.springframework.web.multipart.MultipartFile;

import javax.swing.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @ClassName ExcelUtils
 * @Description Excel工具类
 * @Author HEKE
 * @Date 2019/3/5 10:04
 */
public class ExcelUtils {

    private static int FILED;

    public static int getFILED() {
        return FILED;
    }

    public static void setFILED(int FILED) {
        ExcelUtils.FILED = FILED;
    }

    public static void outPutExcel(String[] heads, Collection<?> data, String path){

        //创建excel
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工作表
        HSSFSheet sheet = wb.createSheet();
        //创建表头
        HSSFRow headRow = sheet.createRow(0);
        for (int i = 0; i < heads.length; i++){
            headRow.createCell(i).setCellValue(heads[i]);
        }

    }


    /**
     * 验证excel
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath){
        if (filePath == null || !isExcel2003(filePath) || !isExcel2007(filePath)){
            return false;
        }
        return true;
    }


    /**
     * 是否是2003的excel，返回true是2003
     * @param filePath
     * @return
     */
    public static boolean isExcel2003(String filePath){
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 是否是2007的excel，返回true是2007
     * @param filePath
     * @return
     */
    public static boolean isExcel2007(String filePath){
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 上传excel，读入数据库
     * @param mFile
     * @return
     */
    public static List<Map<String, Object>> uploadExcel(MultipartFile mFile){

        String fileName = mFile.getOriginalFilename();

        try{
            /*if(!ExcelUtils.validateExcel(fileName)){
                return null;
            }

            boolean isExcel2003 = true;
            if (ExcelUtils.isExcel2007(fileName)){
                isExcel2003 =false;
            }*/
            return createExcel(mFile.getInputStream(), true);
        }catch (Exception e){
            e.printStackTrace();
        }

        return null;
    }


    /**
     * 根据文件名创建对应excel
     * @param is
     * @param isExcel2003
     * @return
     */
    public static List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003){
        try {
            Workbook wb = null;
            if (isExcel2003){
                wb = new HSSFWorkbook(is);
            }else {
                wb = new XSSFWorkbook(is);
            }
            return readExcelValue(wb);
        }catch (IOException e){
            e.printStackTrace();
        }

        return null;
    }


    /**
     * 读取excel内容
     * @param wb
     * @return
     */
    public static List<Map<String, Object>> readExcelValue(Workbook wb){
        //得到第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        //得到excel行数
        int totalRows = sheet.getPhysicalNumberOfRows();
        //得到列数,前提是有行数
        int totalLines = 0;
        if (totalRows > 1 && sheet.getRow(0) != null){
            totalLines = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<Map<String, Object>> result = new ArrayList<>();
        //循环excel行数
        for (int r = 2; r < totalRows; r++){
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            Cell cell4 = row.getCell(4);
            Cell cell5 = row.getCell(5);
            Cell cell6 = row.getCell(6);
            Cell cell7 = row.getCell(7);
            Cell cell9 = row.getCell(9);
            if (cell4 == null && cell5 == null && cell6 == null && cell7 == null && cell9 == null){
                FILED++;
                continue;
            }
            /*if (cell4.toString().equals("null") && cell5.toString().equals("null") && cell6.toString().equals("null")&&
                    cell7.toString().equals("null") && cell9.toString().equals("null")){
                continue;
            }*/

            /*if (cell4.getCellTypeEnum() != CellType.NUMERIC || cell5.getCellTypeEnum() != CellType.NUMERIC ||
                    cell6.getCellTypeEnum() != CellType.NUMERIC || cell7.getCellTypeEnum() != CellType.NUMERIC ||
                    cell9.getCellTypeEnum() != CellType.NUMERIC *//*|| cell4.toString().equals("null") || cell5.toString().equals("null") || cell6.toString().equals("null")||
                    cell7.toString().equals("null") || cell9.toString().equals("null")*//*){
                FILED++;
                continue;
            }*/
            //循环列
            Map<String, Object> map = new HashMap<>();
            for (int c = 0; c < totalLines; c++){
                Cell cell = row.getCell(c);
                if (c == 4 || c == 5 || c == 6 || c == 7 || c == 9){
                    if (cell == null || cell.getCellTypeEnum() != CellType.NUMERIC){
                        map.put(sheet.getRow(0).getCell(c).toString(),"");
                    }else {
                        cell.setCellType(CellType.NUMERIC);
                        map.put(sheet.getRow(0).getCell(c).toString(),cell.toString());
                    }
                }else {
                    if (cell == null || cell.getCellTypeEnum() != CellType.STRING){
                        map.put(sheet.getRow(0).getCell(c).toString(),"");
                    }else {
                        cell.setCellType(CellType.STRING);
                        map.put(sheet.getRow(0).getCell(c).toString(),cell.toString());
                    }
                }
            }
            result.add(map);
        }
        return result;
    }
}
